/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package DBController;

import model.PelaporanBean;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author brlnt
 */
public class PelaporanDB extends DBClass {

    private String sql;
    private Connection con;
    private Statement stmt;
    private ResultSet rs;

    private void connection() {
        try {
            con = createConnection();
            stmt = con.createStatement();
        } catch (SQLException ex) {
            Logger.getLogger(UserDB.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(UserDB.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    private void closeCon() {
        try {
            con.close();
        } catch (SQLException ex) {
            Logger.getLogger(PelaporanDB.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    private ResultSet doQuery(String sql) throws ClassNotFoundException, SQLException {
        con = createConnection();
        stmt = con.createStatement();
        return stmt.executeQuery(sql);
    }

    private int doUpdate(String sql) throws ClassNotFoundException, SQLException {
        con = createConnection();
        stmt = con.createStatement();
        return stmt.executeUpdate(sql);
    }

    /**
     *
     * @return @throws SQLException
     * @throws ClassNotFoundException
     */
    public List getAllPelaporan() throws SQLException, ClassNotFoundException {
        sql = "SELECT * from `pelaporan`";
        return getPelaporan(sql);
    }

    /**
     *
     * @param idUser
     * @return
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    public List getPelaporanByUser(int idUser) throws SQLException, ClassNotFoundException {
        sql = "SELECT * from `pelaporan` WHERE id_user=" + idUser + " ORDER BY `ID_PELAPORAN` DESC";
        return getPelaporan(sql);
    }

    /**
     *
     * @param status
     * @return
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    public List getPelaporanByStatus(int status) {
        try {
            sql = "SELECT p.`ID_PELAPORAN`, p.`ID_USER`, p.`NAMASUNGAI`, p.`LOKASISUNGAI`, "
                    + "p.`FOTOSUNGAI`, p.`DESKRIPSI`, p.`SUHUSUNGAI`, p.`PHSUNGAI`, p.`TIME`, "
                    + "p.`ADANYASAMPAH`, p.`WARNASUNGAI`, p.`BAUSUNGAI`, p.`STATUS`, "
                    + "b.N_BAU_SUNGAI, w.N_WARNA_SUNGAI, a.N_ADANYA_SAMPAH "
                    + "FROM `pelaporan` p "
                    + "INNER JOIN `bau_sungai` b "
                    + "ON p.BAUSUNGAI = b.ID_BAU_SUNGAI "
                    + "INNER JOIN warna_sungai w "
                    + "ON p.WARNASUNGAI = w.ID_WARNA_SUNGAI "
                    + "INNER JOIN adanya_sampah a "
                    + "ON p.ADANYASAMPAH = a.ID_ADANYA_SAMPAH "
                    + "WHERE p.status=" + status + " "
                    + "ORDER BY `ID_PELAPORAN` DESC";
                    //return getPelaporan(sql);
                    connection();
                    rs = doQuery(sql);
                    List listdata = new ArrayList();
                    PelaporanBean dataPelaporan;
                    
                    while (rs.next()) {
                        dataPelaporan = new PelaporanBean();
                        dataPelaporan.setIdPelaporan(rs.getInt(1));
                        dataPelaporan.setIdUser(rs.getInt(2));
                        dataPelaporan.setNamasungai(rs.getString(3));
                        dataPelaporan.setLokasisungai(rs.getString(4));
                        dataPelaporan.setFotosungai(rs.getString(5));
                        dataPelaporan.setDeskripsi(rs.getString(6));
                        dataPelaporan.setSuhusungai(rs.getInt(7));
                        dataPelaporan.setPhsungai(rs.getDouble(8));
                        dataPelaporan.setTime(rs.getString(9));
                        dataPelaporan.setAdanyasampah(rs.getInt(10));
                        dataPelaporan.setWarnasungai(rs.getInt(11));
                        dataPelaporan.setBausungai(rs.getInt(12));
                        dataPelaporan.setStatus(rs.getInt(13));
                        dataPelaporan.setnBausungai(rs.getString(14));
                        dataPelaporan.setnWarnasungai(rs.getString(15));
                        dataPelaporan.setnAdanyasampah(rs.getString(16));
                        listdata.add(dataPelaporan);
                    }
                    // menutup semua koneksi db
                    closeCon();
                    //return value list data pelaporan
                    return listdata;
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(PelaporanDB.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        } catch (SQLException ex) {
            Logger.getLogger(PelaporanDB.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        }
    }

    /**
     *
     * @param id
     * @return
     */
    public List getPelaporanById(int id) {
        try {
            sql = "SELECT * from `pelaporan` WHERE id_pelaporan=" + id;
            return getPelaporan(sql);
        } catch (SQLException ex) {
            Logger.getLogger(PelaporanDB.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(PelaporanDB.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        }
    }

    private List getPelaporan(String sql) throws SQLException, ClassNotFoundException {
        // mempersiapkan list dataPelaporan
        List listdata = new ArrayList();
        //membuat koneksi , createConnection terdapat dalam DBClass
        con = createConnection();
        stmt = con.createStatement();
        rs = stmt.executeQuery(sql);
        // mempersiapkan object dataPelaporan
        PelaporanBean dataPelaporan;

        while (rs.next()) {
            dataPelaporan = new PelaporanBean();
            dataPelaporan.setIdPelaporan(rs.getInt(1));
            dataPelaporan.setIdUser(rs.getInt(2));
            dataPelaporan.setNamasungai(rs.getString(3));
            dataPelaporan.setLokasisungai(rs.getString(4));
            dataPelaporan.setFotosungai(rs.getString(5));
            dataPelaporan.setDeskripsi(rs.getString(6));
            dataPelaporan.setSuhusungai(rs.getInt(7));
            dataPelaporan.setPhsungai(rs.getDouble(8));
            dataPelaporan.setTime(rs.getString(9));
            dataPelaporan.setAdanyasampah(rs.getInt(10));
            dataPelaporan.setWarnasungai(rs.getInt(11));
            dataPelaporan.setBausungai(rs.getInt(12));
            dataPelaporan.setStatus(rs.getInt(13));
            listdata.add(dataPelaporan);
        }
        // menutup semua koneksi db
        closeCon();
        //return value list data pelaporan
        return listdata;
    }

    /**
     *
     * @param dataPelaporan
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    public void insertPelaporan(PelaporanBean dataPelaporan) throws ClassNotFoundException, SQLException {
        // query
        sql = "INSERT INTO `pelaporan`(`ID_USER`, `NAMASUNGAI`, `LOKASISUNGAI`, `FOTOSUNGAI`, `DESKRIPSI`, `STATUS`, `WARNASUNGAI`, `SUHUSUNGAI`, `BAUSUNGAI`, `ADANYASAMPAH`, `PHSUNGAI`) VALUES ("
                + dataPelaporan.getIdUser() + ","
                + "'" + dataPelaporan.getNamasungai() + "',"
                + "'" + dataPelaporan.getLokasisungai() + "',"
                + "'" + dataPelaporan.getFotosungai() + "',"
                + "'" + dataPelaporan.getDeskripsi() + "',"
                + dataPelaporan.getStatus() + ","
                + dataPelaporan.getWarnasungai() + ","
                + dataPelaporan.getSuhusungai() + ","
                + dataPelaporan.getBausungai() + ","
                + dataPelaporan.getAdanyasampah() + ","
                + dataPelaporan.getPhsungai() + ")";
        // memanggil method untuk execute query
        cuPelaporan(sql);
    }

    /**
     *
     * @param dataPelaporan
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    public void updatePelaporan(PelaporanBean dataPelaporan) throws ClassNotFoundException, SQLException {
        // query
        if (dataPelaporan.getFotosungai() == null || dataPelaporan.getFotosungai().equals("")) {
            sql = "UPDATE `pelaporan` SET `NAMASUNGAI`='"
                    + dataPelaporan.getNamasungai() + "',`LOKASISUNGAI`='"
                    + dataPelaporan.getFotosungai() + "',`DESKRIPSI`='"
                    + dataPelaporan.getDeskripsi() + "',`STATUS`="
                    + dataPelaporan.getStatus() + ",`WARNASUNGAI`="
                    + dataPelaporan.getWarnasungai() + ",`SUHUSUNGAI`="
                    + dataPelaporan.getSuhusungai() + ",`BAUSUNGAI`="
                    + dataPelaporan.getBausungai() + ",`ADANYASAMPAH`="
                    + dataPelaporan.getAdanyasampah() + ",`PHSUNGAI`="
                    + dataPelaporan.getPhsungai()
                    + " WHERE `ID_PELAPORAN`=" + dataPelaporan.getIdPelaporan() + ";";
        } else {
            sql = "UPDATE `pelaporan` SET `NAMASUNGAI`='"
                    + dataPelaporan.getNamasungai() + "',`LOKASISUNGAI`='"
                    + dataPelaporan.getLokasisungai() + "',`FOTOSUNGAI`='"
                    + dataPelaporan.getFotosungai() + "',`DESKRIPSI`='"
                    + dataPelaporan.getDeskripsi() + "',`STATUS`="
                    + dataPelaporan.getStatus() + ",`WARNASUNGAI`="
                    + dataPelaporan.getWarnasungai() + ",`SUHUSUNGAI`="
                    + dataPelaporan.getSuhusungai() + ",`BAUSUNGAI`="
                    + dataPelaporan.getBausungai() + ",`ADANYASAMPAH`="
                    + dataPelaporan.getAdanyasampah() + ",`PHSUNGAI`="
                    + dataPelaporan.getPhsungai()
                    + " WHERE `ID_PELAPORAN`=" + dataPelaporan.getIdPelaporan() + ";";
        }
        // memanggil method untuk execute query
        cuPelaporan(sql);
    }

    /**
     *
     * @param id
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    public void deletePelaporan(int id) throws ClassNotFoundException, SQLException {
        sql = "DELETE FROM `pelaporan` WHERE `ID_PELAPORAN`=" + id;
        cuPelaporan(sql);
    }

    private void cuPelaporan(String sql) throws ClassNotFoundException, SQLException {
        // membuat koneksi
        con = createConnection();
        stmt = con.createStatement();
        // execute query
        stmt.execute(sql);
        // menutup koneksi db
        closeCon();
    }

}
